﻿CREATE PROCEDURE [dbo].[proc_Project_State_GetList]
(
	@CompanyId int,
	@ProductId int,
	@TypeId int,
	@ProjectFinish int,
	@FinanceAudit int,
	@StartDate varchar(20),
	@EndDate varchar (20),
	@Stext nvarchar(50),
	@StartIndex int,
	@EndIndex int
)
AS
Begin
	DECLARE @cSql nvarchar(4000)

	If @CompanyId>0
		set @cSql='CompanyId='+Convert(varchar(10),@CompanyId)+' And CreateDate>=@sRq And CreateDate<=@eRq'
	Else
		set @cSql='CreateDate>=@sRq And CreateDate<=@eRq'

	Set @cSql = @cSql + ' And ProductId='+Convert(varchar(10),@ProductId)
	Set @cSql = @cSql + ' And NOE_Flag=0 And IsHedge=0'

	If @TypeId<>100
		Set @cSql=@cSql+' And TypeId='+Convert(varchar(10),@TypeId)
	If @ProjectFinish<>100
		Set @cSql=@cSql+' And IsFinish='+Convert(varchar(10),@ProjectFinish)
	If @FinanceAudit<>100
		Set @cSql=@cSql+' And FinanceAudit='+Convert(varchar(10),@FinanceAudit)
	If @SText<>''
		Set @cSql=@cSql+' And CusName Like ''%'+@SText+'%'''

Set @cSql='Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
	Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')

	;WITH List As (Select ROW_NUMBER() OVER (Order By CreateDate Desc) AS Row,
		Id,OrderId,CusName,ProductName,
		CreateDate,FinanceAudit,IsFinish
		From [Project] a
		Where '+@cSql + '
	)
	Select a.*,IsNull(b.YingShouPrice,0) As SalePrice,
	IsNull(b.ShiShouPrice,0) As CostPrice,
	(Select Count(0) From list) As RecordCount
	From List a JOIN [Order] b ON a.OrderId=b.OId
	Where Row Between '+Convert(varchar(10),@StartIndex)+' and '+Convert(varchar(10),@EndIndex)+' Order By Row
'
Exec(@cSql)
--Select  @cSql
End
